﻿using System;
using System.Web;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Forms;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Web;
using System.Data;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using System.IO;
using System.Configuration;


namespace CSSAdriverapp
{
    /// <summary>
    /// Interaction logic for AvailableDrivers.xaml
    /// </summary>
    public partial class AvailableDrivers : Window
    {
        int starth, startm, endh, endm;

        public AvailableDrivers()
        {
            InitializeComponent();
        }


        public List<Driver> drivers
        {
            get;
            set;
        }

        DataTable driverSchedule = new DataTable();  //creates data table 
        DateTime start = new DateTime();
        DateTime end = new DateTime();
        private void searchButton_Click(object sender, RoutedEventArgs e)
        {
            
            if (drivers != null)
            {
                drivers.Clear();
                ExportButton.Visibility = Visibility.Hidden; //Initially sets ExportButton to hidden when program is ran
            }

            

            if (shour.SelectedItem != null && smin.SelectedItem != null && sampm.SelectedItem != null && ehour.SelectedItem != null && emin.SelectedItem != null && eampm.SelectedItem != null)
            {
                if (sampm.Text == "PM" && eampm.Text == "AM" && ehour.Text.ToString() != "12")
                {
                    string message1 = "Please select a proper timeframe (I.E. Make sure there isn't P.M. then A.M.)";
                    string caption1 = "Availability";
                    MessageBoxResult result1 = System.Windows.MessageBox.Show(message1, caption1,
                                             MessageBoxButton.OK,
                                             MessageBoxImage.Exclamation);
                }
                else
                {

                    //-------Get Values from Combo Boxes-------------


                    // Set Hours
                    if (sampm.Text == "PM")
                    {
                        if (shour.Text.ToString() == "12")
                        {
                            starth = 12;
                        }
                        else
                        {
                            starth = int.Parse(shour.Text.ToString()) + 12;
                        }
                    }
                    else // am
                    {
                        if (shour.Text.ToString() == "12" && smin.Text.ToString() == "00")
                        {
                            starth = 0;
                        }
                        else if (shour.Text.ToString() == "12" && smin.Text.ToString() == "30")
                        {
                            starth = 0;
                        }
                        else
                        {
                            starth = int.Parse(shour.Text.ToString());
                        }
                    }

                    if (eampm.Text == "PM")
                    {
                        if (ehour.Text.ToString() == "12")
                        {
                            endh = 12;
                        }
                        else
                        {
                            endh = int.Parse(ehour.Text.ToString()) + 12;
                        }
                    }
                    else // am
                    {                                                         
                        if (ehour.Text.ToString() == "12" && emin.Text.ToString() == "00")
                        {
                            endh = 23;
                        }
                        else if (ehour.Text.ToString() == "12" && emin.Text.ToString() == "30")
                        {
                            endh = 0;
                        }
                        else
                        {
                            endh = int.Parse(ehour.Text.ToString());
                        }                    
                    }



                    // Set Minutes
                    if (shour.Text.ToString() == "12" && smin.Text.ToString() == "00") // correct midnight error: Start
                    {
                        startm = 1;
                    }
                    else
                    {
                        startm = int.Parse(smin.Text.ToString());
                    }

                    if (ehour.Text.ToString() == "12" && emin.Text.ToString() == "00") // correct midnight error: End
                    {
                        endm = 59;
                    }
                    else
                    {
                        endm = int.Parse(emin.Text.ToString());
                    }
                    
                    
                    //------------------------------


                    DataClassesDataContext db = new DataClassesDataContext();
                    IQueryable<Driver> driverstemp = from i in db.Drivers select i;
                    List<Driver> availabledrivers = new List<Driver>();
                    foreach (Driver d in driverstemp)
                    {
                        IQueryable<driver_availability> availabilities = from a in db.driver_availabilities where a.driverID == d.ID select a;
                        if (availabilities.Count() > 0)
                        {
                            
                            if (isAvailable(d, availabilities.ToList(), starth, startm, endh, endm, day.Text.ToString()))
                            {
                                availabledrivers.Add(d);
                                ExportButton.Visibility = Visibility.Visible;   //makes button visible if drivers are available
                            }

                        }
                        
                    }
                    
                    drivers = availabledrivers;  //creates local list drive
                    driverSchedule.Clear();
                    driverSchedule.Columns.Clear();
                    driverSchedule.Columns.Add("First Name");   //creates columns in datatable DriverSchedule with with headers. 
                    driverSchedule.Columns.Add("Last Name");
                    driverSchedule.Columns.Add("Email");
                    driverSchedule.Columns.Add("Phone Number");
                    for (int i = 0; i <= drivers.Count - 1; i++)   //fills rows in datatable with available driver data attached to the datagrid
                    {
                        DataRow dr = driverSchedule.NewRow();      
                        dr[0] = drivers[i].first_name;
                        dr[1] = drivers[i].last_name;
                        dr[2] = drivers[i].EMail;
                        dr[3] = drivers[i].PhoneNumber;
                        driverSchedule.Rows.Add(dr);
                    }
                    dataGrid1.ItemsSource = drivers;

                }

            }

            else
            {
                string message = "Please make sure all fields are selected";
                string caption = "Availability";
                MessageBoxResult result = System.Windows.MessageBox.Show(message, caption,
                                         MessageBoxButton.OK,
                                         MessageBoxImage.Exclamation);
                
            }
        }

        private static Boolean isAvailable(Driver d, List<driver_availability> availabilities, int starth, int startm, int endh, int endm, string day)
        {
            foreach (driver_availability a in availabilities)
            {
                
                if (a.day == day) //check if the day matches
                {

                    DateTime start = (DateTime)a.startTime;
                    DateTime end = (DateTime)a.endTime;

                    DateTime searchstart = new DateTime(start.Year, start.Month, start.Day, starth, startm, start.Second);
                    DateTime searchend = new DateTime(end.Year, end.Month, end.Day, endh, endm, end.Second);
                    searchend = new DateTime(end.Year, end.Month, end.Day, endh, endm, end.Second);
                    if (searchstart >= start && searchend <= end)
                    {

                        return true;

                    }
           
                }
           
            }

            return false;
        }

        private void exportButton_Click(object sender, RoutedEventArgs e)
        {
            //  Displays a SaveFileDialog 
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.FileName = ("Drivers Available " + day.Text.ToString() + " " + shour.Text.ToString() + "-" + smin.Text.ToString() + sampm.Text.ToString() + " To " + ehour.Text.ToString() + "-" + emin.Text.ToString() + eampm.Text.ToString());
            saveFileDialog1.Filter = "Excel Worksheets (*.xls, *.xlsx)|*.xls;*.xlsx";
            saveFileDialog1.Title = "Save an Excel File";
            saveFileDialog1.ShowDialog();



            // If the file name is not an empty string open it for saving.
            if (saveFileDialog1.FileName != "")
            {
                try
                {
                    // Saves FileStream created by the OpenFile method.
                    System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog1.OpenFile();
                    fs.Close();
                }
                catch (Exception ex)
                {
                    string Message = "The file you are trying to save over is open in another process. Please close the file or save in a different name.";
                    string questionCaption = "File Open in Another Process";
                    MessageBoxResult message3 = System.Windows.MessageBox.Show(Message, questionCaption, MessageBoxButton.OK, MessageBoxImage.Warning);
                }
            }

            if (saveFileDialog1.FileName != "")
                {
                DataColumnCollection dc = driverSchedule.Columns;
                try
                {
                    using (FileStream stream = new FileStream(saveFileDialog1.FileName, FileMode.Create, FileAccess.Write)) //creates an xls files and writes it to the location of the file
                    {
                        HSSFWorkbook workbook = new HSSFWorkbook(); //creates an excel workbook
                        ISheet sheet1 = workbook.CreateSheet("Sheet1"); //creates an excel sheet
                        NPOI.SS.UserModel.IFont font = workbook.CreateFont();
                        font.Boldweight = (short)FontBoldWeight.Bold;
                        ICellStyle style1 = workbook.CreateCellStyle();
                        style1.SetFont(font);
                        for (int i = 0; i < driverSchedule.Rows.Count + 1; i++)
                        {
                            IRow row = sheet1.CreateRow(i); //class within NPOI that references rows
                            for (int j = 0; j < driverSchedule.Columns.Count; j++)
                            {

                                ICell cell = row.CreateCell(j); //class within NPOI that references cells within excel
                                
                                if (i == 0)
                                {

                                    cell.SetCellValue(dc[j].ToString()); //if the first row is located, then adds columns from DataColumnCollection.
                                    cell.CellStyle = style1;

                                }
                                    
                                else
                                {
                                    cell.SetCellValue(driverSchedule.Rows[i - 1].ItemArray[j].ToString());
                                    
                                }
                                sheet1.AutoSizeColumn(i);
                                sheet1.AutoSizeColumn(j);

                            }
                        }
                        
                        workbook.Write(stream); //writes data to stream

                    }
                    driverSchedule.AcceptChanges();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
            }

            }

        private void shour_SelectionChanged(object sender, SelectionChangedEventArgs e)  //method that fills in additional minute and meridian boxes
        {
            if (shour.SelectedItem != null)
            {
                smin.SelectedIndex = 0;
                sampm.SelectedIndex = 0;
                
            }
        }

        private void ehour_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (ehour.SelectedItem != null)
            {
                emin.SelectedIndex = 0;
                eampm.SelectedIndex = 0;
            }
        }
    
    }
}
